﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SQLite;
namespace youdaoImport
{
    public class sqliteEngineer
    {
        public static DataTable GetDateBySql(string connectString, string sql)
        {

            SQLiteConnection conn = new SQLiteConnection(connectString);
            SQLiteCommand com = new SQLiteCommand();
            com.Connection = conn;
            com.CommandText = sql;
            SQLiteDataAdapter oda = new SQLiteDataAdapter(com);
            DataTable dt = new DataTable();
            oda.Fill(dt);
            return dt;
        }
        public static int ExecuteBySql(string connectString, string sql)
        {
            SQLiteConnection conn = new SQLiteConnection(connectString);
            SQLiteCommand com = new SQLiteCommand();
            com.Connection = conn;
            com.CommandText = sql;
            conn.Open();
            return com.ExecuteNonQuery();
        }
        public static int ExecuteBySqlList(string connectString, List<string> sqllist)
        {
            int iResult=0;
            int commitStep = 10;
            //10条一提交
            int commits = sqllist.Count / commitStep;
            if (sqllist.Count % commitStep != 0) commits += 1;
            string[] strCommits = new string[commits];
            int commitsIndex = 0;
            StringBuilder sbCommits = new StringBuilder();
            for (int i = 0; i < sqllist.Count; i++)
            {
                sbCommits.Append(sqllist[i]);
                sbCommits.Append(";");
                commitsIndex = i / commitStep;
                if ((i % commitStep == commitStep-1)||(i == sqllist.Count - 1))
                {
                    strCommits[commitsIndex] = sbCommits.ToString();
                    sbCommits.Clear();
                }
                
            }

            SQLiteConnection conn = new SQLiteConnection(connectString);
            conn.Open();
            var transaction=conn.BeginTransaction();
            SQLiteCommand com = transaction.Connection.CreateCommand();
            
            try
            {
                for (int index = 0; index < strCommits.Length; index++)
                {
                    com.CommandText = strCommits[index];
                    iResult += com.ExecuteNonQuery();
                }
                transaction.Commit();
            }
            catch(Exception ep)
            {
                transaction.Rollback();
                throw new Exception(ep.Message, ep);
            }
            finally
            {
                conn.Close();
            }
            return iResult;
        }
    }
}
